package com.tsingtree.base.db.util;

import cn.hutool.core.util.StrUtil;
import com.tsingtree.base.db.model.TableFieldInfo;
import com.tsingtree.trees.core.config.properties.DruidProperties;
import com.tsingtree.trees.kernel.model.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库操作工具类
 *
 * @author jagger
 */
@Slf4j
public class DbUtil {

    public static List<Map<String, Object>> selectTables(DruidProperties druidProperties) {
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        try {
            Class.forName(druidProperties.getDriverClassName());
            Connection conn = DriverManager.getConnection(druidProperties.getUrl(), druidProperties.getUsername(), druidProperties.getPassword());

            //获取数据库名称
            String dbName = getDbName(druidProperties.getUrl());

            PreparedStatement preparedStatement = conn.prepareStatement(
                    "select TABLE_NAME as tableName,TABLE_COMMENT as tableComment from information_schema.`TABLES` where TABLE_SCHEMA = '" + dbName + "'");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                HashMap<String, Object> map = new HashMap<>();
                String tableName = resultSet.getString("tableName");
                String tableComment = resultSet.getString("tableComment");
                map.put("tableName", tableName);
                map.put("tableComment", tableComment);
                list.add(map);
            }
            return list;
        } catch (Exception ex) {
            log.error("执行sql出现问题！", ex);
            return null;
        }
    }

    public static List<TableFieldInfo> getTableFields(String tableName, DruidProperties druidProperties) {
        ArrayList<TableFieldInfo> fieldList = new ArrayList<>();
        try {
            Class.forName(druidProperties.getDriverClassName());
            Connection conn = DriverManager.getConnection(
                    druidProperties.getUrl(), druidProperties.getUsername(), druidProperties.getPassword());
            String sql = "select COLUMN_NAME as columnName,COLUMN_COMMENT as columnComment, IS_NULLABLE as isNullable, DATA_TYPE as dataType from information_schema.COLUMNS where table_name = ? and table_schema = ?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            String dbName = getDbName(druidProperties.getUrl());
            preparedStatement.setString(1, tableName);
            preparedStatement.setString(2, dbName);
            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                TableFieldInfo tableFieldInfo = new TableFieldInfo();
                String columnName = resultSet.getString("columnName");
                String columnComment = resultSet.getString("columnComment");
                Boolean isNullable = "YES".equals(resultSet.getString("isNullable"));
                String dataType = resultSet.getString("dataType");
                tableFieldInfo.setColumnName(columnName);
                tableFieldInfo.setColumnComment(columnComment);
                tableFieldInfo.setDataType(dataType);
                tableFieldInfo.setIsNullable(isNullable);
                tableFieldInfo.setCamelFieldName(StrUtil.toCamelCase(columnName));
                fieldList.add(tableFieldInfo);
            }
            return fieldList;
        } catch (Exception ex) {
            log.error("查询表的所有字段错误！", ex);
            throw new ServiceException(500, "查询数据库中数据源信息错误");
        }
    }

    /**
     * 获取数据库名称
     *
     * @author jagger
     */
    private static String getDbName(String jdbcUrl) {
        int first = jdbcUrl.lastIndexOf("/") + 1;
        int last = jdbcUrl.indexOf("?");
        return jdbcUrl.substring(first, last);
    }
}
